﻿using Common.DbHelper;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace DAL
{
  public class BookDAL
    {
      public DataTable GetBookBycondtion(int pageindex, int pagesize)
      {
           
          string query = string.Empty;
          if (pagesize <= 0) pagesize = 1;
          if (pageindex <= 1)
          {
              query = "Select top {0} ID,Format(Time,'yyyy-mm-dd'),s1,s2,s3,s4,s5,s6,s7,s8  From {1} Order by {2}";
              query = string.Format(query, pagesize, "book",  " ID DESC ");
          }
          else
          {
              query = "select a.* from ( select top {0} ID,Format(Time,'yyyy-mm-dd'),s1,s2,s3,s4,s5,s6,s7,s8  from {1}  Order by {2} ) a left join ( select top {3} ID,Format(Time,'yyyy-mm-dd'),s1,s2,s3,s4,s5,s6,s7,s8  from {4}  Order by {5} ) b on a.id=b.id where iif(b.id,'0','1')='1'";
              query = string.Format(query, pageindex * pagesize, "book", " ID DESC ", (pageindex - 1) * pagesize,"book"," ID DESC ");
          }


          return DbHelperMySQL.Query(query).Tables[0];
      }
      public object GetBookBycondtionCount()
      {
          string strSql = string.Format(@"select count(*) from book  ");

          return DbHelperMySQL.GetSingle(strSql);
      }
      public bool Update(Book model)
      {
          StringBuilder strSql = new StringBuilder();
          strSql.Append("update book set ");

           
          if (model.rebook != null)
          {
              strSql.Append("rebook='" + model.rebook + "',");
          }

           
          int n = strSql.ToString().LastIndexOf(",");
          strSql.Remove(n, 1);
          strSql.Append(" where id=" + model.ID + "");
          int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
          if (rowsAffected > 0)
          {
              return true;
          }
          else
          {
              return false;
          }
      }
      public Book GetModel(int id)
      {
          StringBuilder strSql = new StringBuilder();
          strSql.Append("select  ");
          strSql.Append(" * ");
          strSql.Append(" from book ");
          strSql.Append(" where id=" + id + "");
          Book model = new Book();
          DataSet ds = DbHelperMySQL.Query(strSql.ToString());
          if (ds.Tables[0].Rows.Count > 0)
          {
              return DataRowToModel(ds.Tables[0].Rows[0]);
          }
          else
          {
              return null;
          }
      }

      public Book DataRowToModel(DataRow row)
      {
          Book model = new Book();
          if (row != null)
          {
              if (row["ID"] != null && row["ID"].ToString() != "")
              {
                  model.ID = int.Parse(row["ID"].ToString());
              }

              if (row["Time"] != null)
              {
                  model.Time =Convert.ToDateTime(row["Time"].ToString());
              }
              if (row["s1"] != null)
              {
                  model.s1 = row["s1"].ToString();
              }

              if (row["s2"] != null)
              {
                  model.s2 = row["s2"].ToString();
              }
              if (row["s3"] != null)
              {
                  model.s3 = row["s3"].ToString();
              }
              if (row["s4"] != null)
              {
                  model.s4 = row["s4"].ToString();
              }
              if (row["s5"] != null)
              {
                  model.s5 = row["s5"].ToString();
              }
              if (row["s6"] != null)
              {
                  model.s6 = row["s6"].ToString();
              }
              if (row["s7"] != null)
              {
                  model.s7 = row["s7"].ToString();
              }
              if (row["s8"] != null)
              {
                  model.s8 = row["s8"].ToString();
              }
              if (row["rebook"] != null)
              {
                  model.rebook = row["rebook"].ToString();
              }
          }
          return model;
      }
        
    }
}
